package com.jandar.util;

import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

import com.jandar.DataBaseType;
import com.jandar.Resources;
import com.jandar.generator.IdGenerator;
import com.jandar.table.config.Column;
import com.jandar.table.config.ColumnTypeInfo;
import com.jandar.table.config.Id;
import com.jandar.table.config.IdGeneratorInfo;
import com.jandar.table.config.Table;
import com.jandar.table.config.UniqueInfo;

/**
 * SQL文生成工具类。
 * 
 * @author pqj
 * @version 2009-5-31
 */
public class SQLUtils {

	/** 插入语句模板 */
	private static final String INSERT_SQL = "insert into {0} ({1}) values({2})";

	/** 更新语句模板 */
	private static final String UPDATE_SQL = "update {0} set {1} {2}";

	/** 查询语句模板 */
	private static final String SELECT_COUNT_SQL = "select count(*) from {0} {1}";

	/** 插入选项 */
	private static final int INSERT_OPTION = 1;

	/** 更新选项 */
	private static final int UPDATE_OPTION = 2;

	/** MYSQL日期时间函数 */
	private static final String MYSQL_STR_TO_DATE = "STR_TO_DATE({date}, {format})";

	/** MYSQL日期匹配模式 */
	private static final String MYSQL_DATE_PATTERN = "%Y-%m-%d";

	/** MYSQL日期时间匹配模式 */
	private static final String MYSQL_DATETIME_PATTERN = "%Y-%m-%d %H:%i:%s";

	/** ORACLE日期函数 */
	private static final String ORACLE_TO_DATE = "TO_DATE({date}, {format})";

	/** ORACLE日期时间函数 */
	private static final String ORACLE_TO_TIMESTAMP = "TO_TIMESTAMP({date}, {format})";

	/** ORACLE日期匹配模式 */
	private static final String ORACLE_DATE_PATTERN = "YYYY-MM-DD";

	/** ORACLE日期时间匹配模式 */
	private static final String ORACLE_DATETIME_PATTERN = "YYYY-MM-DD HH24:MI:SS";

	/** MSSQL数据类型转换函数 */
	private static final String MSSQL_CONVERT = "CONVERT({type}, {expression}, {style}) ";

	/** MSSQL类型转换函数 */
	private static final String MSSQL_CAST = "CAST({date} AS {type})";

	/** MSSQL时间类型 */
	private static final String MSSQL_DATETIME_TYPE = "DATETIME";

	/** 布尔值为真 */
	private static final String BOOLEAN_TRUE = "1";

	/** 布尔值为假 */
	private static final String BOOLEAN_FALSE = "0";

	/** 接受的日期字符串模式 */
	public static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd";

	/** 接受的日期时间字符串模式 */
	public static final String DEFAULT_DATETIME_PATTERN = "yyyy-MM-dd HH:mm:ss";

	/** 数字匹配模式 */
	private static final String NUMBER_PATTERN = "^[0-9]+[\\.]*[0-9]*";

	/** 数字0匹配模式 */
	private static final String NUMBER_FALSE_PATTERN = "^[0]+[\\.]*[0]*";

	/** 数据库类型 */
	private static DataBaseType dataBaseType = null;

	/** 数据库表配置 */
	private static Map<String, Table> tableCfgMap = null;

	/** 布尔型导入值 True */
	private static List<String> boolTrue = null;

	/** 布尔型导入值 False */
	private static List<String> boolFalse = null;

	private static final boolean hasEmptyField = Resources.getInstance().isHasEmptyField();

	static {
		Resources resources = Resources.getInstance();
		dataBaseType = resources.getDataBaseType();
		tableCfgMap = resources.getTableCfgMap();
		boolTrue = resources.getBoolTrue();
		boolFalse = resources.getBoolFalse();
	}

	private SQLUtils() {
	}

	/**
	 * 创建Insert语句。
	 * 
	 * @param tabNameTitle 表名
	 * @param values 值
	 * @throws Exception 异常
	 * 
	 * @return SQL语句
	 */
	public static String createInsertSQL(String tableName, String[] values) throws Exception {
		if (tableName == null || "".equals(tableName) || values == null || values.length == 0) {

			return null;
		}
		Table table = tableCfgMap.get(tableName);
		if (table != null) {
			List<String> fields = new ArrayList<String>(values.length);
			List<String> fmtValues = new ArrayList<String>(values.length);
			resolveInsert(table, values, fields, fmtValues);
			return fillPattern(INSERT_SQL, tableName, splitString(fields.toArray()), splitString(fmtValues.toArray()));
		} else {
			throw new Exception("找不到表：" + tableName + "的配置信息！");
		}
	}

	/**
	 * 创建Update语句。
	 * 
	 * @param tableName 表名
	 * @param values 值
	 * @throws Exception 异常
	 * @return SQL语句
	 */
	public static String createUpdateSQL(String tableName, String[] values) throws Exception {
		if (tableName == null || "".equals(tableName) || values == null || values.length == 0) {

			return null;
		}

		Table table = tableCfgMap.get(tableName);
		if (table != null) {
			List<String> setValues = new ArrayList<String>(table.getColumn().size());
			resolveUpdate(table, values, setValues);
			return fillPattern(UPDATE_SQL, tableName, splitString(setValues.toArray()), getConditionSQL(table, values));
		} else {
			throw new Exception("找不到表：" + tableName + "的配置信息！");
		}
	}

	/**
	 * 创建查询是否存在记录SQL语句。
	 * 
	 * @param tableName 表名
	 * @param values 值
	 * @throws Exception 异常
	 * @return SQL语句
	 */
	public static String createSelectCountSQL(String tableName, String[] values) throws Exception {
		if (tableName == null || "".equals(tableName) || values == null || values.length == 0) {

			return null;
		}

		Table table = tableCfgMap.get(tableName);
		if (table != null) {
			return fillPattern(SELECT_COUNT_SQL, tableName, getConditionSQL(table, values));
		} else {
			throw new Exception("找不到表：" + tableName + "的配置信息！");
		}
	}

	/**
	 * 检测指定数据库表是否存在。
	 * 
	 * @param tableName 表名
	 * @return SQL语句
	 */
	public static String createCheckTableSQL(String tableName) {
		return "select count(*) from " + tableName;
	}

	private static void resolveInsert(Table table, String[] values, List<String> fields, List<String> fmtValues)
			throws Exception {
		List<Id> ids = table.getId();
		List<Column> columns = table.getColumn();
		IdGeneratorInfo generator = getIdGenerator(ids);

		int index = 0;
		switch (generator) {
		case AUTO:
			// 不需要插入ID，单个。
			index = (hasEmptyField) ? ids.size() : 0;
			for (Column col : columns) {
				if (col.getType() != ColumnTypeInfo.VIRTUAL) {
					fields.add(col.getValue());
					fmtValues.add(getFormatColumnValue(col, values[index], INSERT_OPTION));
				}
				++index;
			}
			break;

		case SEQUENCE:
			// 通过序列插入ID，单个。
			Id id = ids.get(0);
			fields.add(id.getValue());
			fmtValues.add(id.getSequence());
			index = (hasEmptyField) ? ids.size() : 0;
			for (Column col : columns) {
				if (col.getType() != ColumnTypeInfo.VIRTUAL) {
					fields.add(col.getValue());
					fmtValues.add(getFormatColumnValue(col, values[index], INSERT_OPTION));
				}
				++index;
			}
			break;

		case INSERT:
			// 直接插入ID，一个或多个。
			index = 0;
			for (Id item : ids) {
				fields.add(item.getValue());
				fmtValues.add(getFormatColumnValue(item, values[index++], INSERT_OPTION));
			}
			for (Column col : columns) {
				if (col.getType() != ColumnTypeInfo.VIRTUAL) {
					fields.add(col.getValue());
					fmtValues.add(getFormatColumnValue(col, values[index], INSERT_OPTION));
				}
				++index;
			}
			break;
		
		case DEFINE:
			// 通过自定义方式插入ID，单个。
			Id defId = ids.get(0);
			fields.add(defId.getValue());
			fmtValues.add((String) createId(defId, Resources.getInstance().getIdGenerator()));
			index = (hasEmptyField) ? ids.size() : 0;
			for (Column col : columns) {
				if (col.getType() != ColumnTypeInfo.VIRTUAL) {
					fields.add(col.getValue());
					fmtValues.add(getFormatColumnValue(col, values[index], INSERT_OPTION));
				}
				++index;
			}
			break;
		}
	}
	
	private static Object createId(Id id, IdGenerator gen) {
		Object obj = gen.generate();
		ColumnTypeInfo type = id.getType();
		Object result = null;
		if (ColumnTypeInfo.NUMBER.equals(type)) {
			result = Long.valueOf(obj.toString());
		} else if (ColumnTypeInfo.STRING.equals(type)) {
			result = "'" + obj.toString() + "'";
		} else {
			throw new RuntimeException("主键类型必须是数字或字符串！");
		}
		return result;
	}

	private static void resolveUpdate(Table table, String[] values, List<String> setValues) throws Exception {
		List<Id> ids = table.getId();
		List<Column> columns = table.getColumn();
		IdGeneratorInfo generator = getIdGenerator(ids);

		int index = 0;
		switch (generator) {
		case AUTO:
		case SEQUENCE:
			// 更新唯一键以外的字段
			index = (hasEmptyField) ? ids.size() : 0;
			for (Column col : columns) {
				if ((col.getType() != ColumnTypeInfo.VIRTUAL)
						&& (col.getUnique() == null || col.getUnique() == UniqueInfo.FALSE)) {
					setValues.add(getFormatColumnValue(col, values[index], UPDATE_OPTION));
				}
				++index;
			}
			break;
		case INSERT:
			// 更新ID以外的字段
			index = ids.size();
			for (Column col : columns) {
				if (col.getType() != ColumnTypeInfo.VIRTUAL) {
					setValues.add(getFormatColumnValue(col, values[index], UPDATE_OPTION));
				}
				++index;
			}
			break;
		}
	}

	private static String getConditionSQL(Table table, String[] values) throws Exception {
		List<Id> ids = table.getId();
		List<Column> columns = table.getColumn();
		IdGeneratorInfo generator = getIdGenerator(ids);
		StringBuilder buf = new StringBuilder();
		int index = 0;
		switch (generator) {
		case AUTO:
		case SEQUENCE:
			// 使用ID以外的唯一键查询记录是否存在
			index = (hasEmptyField) ? ids.size() : 0;
			for (Column col : columns) {
				if (col.getType() != ColumnTypeInfo.VIRTUAL && col.getUnique() == UniqueInfo.TRUE) {

					if (isUniqueNull(values[index])) {
						throw new Exception("唯一键不能为空.");
					}

					if (buf.length() > 0) {
						buf.append(" and ");
					}
					buf.append(getFormatColumnValue(col, values[index], UPDATE_OPTION));
				}
				++index;
			}
			break;
		case INSERT:
			// 使用ID查询记录是否存在
			index = 0;
			for (Id id : ids) {
				if (isUniqueNull(values[index])) {
					throw new Exception("唯一键不能为空.");
				}

				if (buf.length() > 0) {
					buf.append(" and ");
				}
				buf.append(getFormatColumnValue(id, values[index++], UPDATE_OPTION));
			}
			break;
		}
		return (buf.length() > 0) ? "where " + buf.toString() : "where 1=0";
	}

	private static boolean isUniqueNull(String value) {
		return (value == null || "null".equals(value.toLowerCase()) || "".equals(value)) ? true : false;
	}

	public static IdGeneratorInfo getIdGenerator(List<Id> ids) throws Exception {
		// 多个ID必须是INSERT
		if (ids.size() > 1) {
			for (int i = 0; i < ids.size(); ++i) {
				if (IdGeneratorInfo.INSERT != ids.get(i).getGenerator()) {
					throw new Exception("多个主键的表只能选择generator=\"INSERT\"");
				}
			}
			return IdGeneratorInfo.INSERT;
			// 单个ID，返回主键生成策略
		} else if (ids.size() == 1) {
			return ids.get(0).getGenerator();
			// 没有ID
		} else {
			throw new Exception("不存在主键异常！");
		}
	}

	/**
	 * 根据配置信息包装导入数据。
	 * 
	 * @param generator 主键策略
	 * @param idSize ID个数
	 * @param args 参数
	 * @return 包装参数
	 */
	public static String[] createValues(IdGeneratorInfo generator, int idSize, String... args) {
		String[] ret = null;
		int index = 0;
		int count = 0;

		if (IdGeneratorInfo.AUTO == generator || IdGeneratorInfo.SEQUENCE == generator) {
			if (hasEmptyField) {
				count = idSize + args.length;
				index = idSize;
				ret = new String[count];
			} else {
				index = 0;
				count = args.length;
				ret = new String[count];
			}
		} else {
			index = 0;
			count = args.length;
			ret = new String[count];
		}
		for (int i = 0; index < count; ++index, ++i) {
			ret[index] = args[i];
		}
		return ret;
	}

	private static String splitString(Object[] args) {
		StringBuilder buf = new StringBuilder();
		for (int i = 0; i < args.length; ++i) {
			if (i > 0) {
				buf.append(", ");
			}
			buf.append((args[i] != null) ? args[i].toString() : null);
		}
		return buf.toString();
	}

	private static String fillPattern(String pattern, Object... arguments) {
		return MessageFormat.format(pattern, arguments);
	}

	private static String getFormatColumnValue(Column column, String value, int option) throws Exception {
		String ret = null;
		ColumnTypeInfo type = column.getType();

		if (value == null) {
			ret = null;
		} else {
			switch (type) {
			case NUMBER:
				ret = value;
				break;
			case STRING:
				ret = "\'" + value + "\'";
				break;
			case DATE:
				switch (dataBaseType) {
				case MYSQL:
					ret = MYSQL_STR_TO_DATE.replace("{date}", "\'" + value + "\'");
					ret = ret.replace("{format}", "\'" + MYSQL_DATE_PATTERN + "\'");
					break;
				case ORACLE:
					ret = ORACLE_TO_DATE.replace("{date}", "\'" + value + "\'");
					ret = ret.replace("{format}", "\'" + ORACLE_DATE_PATTERN + "\'");
					break;
				case SQLSERVER:
					ret = MSSQL_CAST.replace("{date}", "\'" + value + "\'");
					ret = ret.replace("{type}", MSSQL_DATETIME_TYPE);
					break;
				}
				break;
			case DATETIME:
				switch (dataBaseType) {
				case MYSQL:
					ret = MYSQL_STR_TO_DATE.replace("{date}", "\'" + value + "\'");
					ret = ret.replace("{format}", "\'" + MYSQL_DATETIME_PATTERN + "\'");
					break;
				case ORACLE:
					ret = ORACLE_TO_TIMESTAMP.replace("{date}", "\'" + value + "\'");
					ret = ret.replace("{format}", "\'" + ORACLE_DATETIME_PATTERN + "\'");
					break;
				case SQLSERVER:
					ret = MSSQL_CAST.replace("{date}", "\'" + value + "\'");
					ret = ret.replace("{type}", MSSQL_DATETIME_TYPE);
					break;
				}
				break;
			case BOOLEAN:
				String boolValue = value.toLowerCase();
				if (boolTrue.contains(boolValue)) {
					ret = BOOLEAN_TRUE;
				} else if (boolFalse.contains(boolValue)) {
					ret = BOOLEAN_FALSE;
				} else if (isNumber(boolValue)) {
					if (numToBool(boolValue)) {
						ret = BOOLEAN_TRUE;
					} else {
						ret = BOOLEAN_FALSE;
					}
				} else {
					throw new Exception("布尔值：" + value + "不能识别！");
				}
				break;
			}
		}

		if (UPDATE_OPTION == option) {
			ret = column.getValue() + "=" + ret;
		}
		return ret;
	}

	private static boolean isNumber(String num) {
		return Pattern.matches(NUMBER_PATTERN, num);
	}

	private static boolean numToBool(String num) {
		return (Pattern.matches(NUMBER_FALSE_PATTERN, num)) ? false : true;
	}
}
